#!/usr/bin python
# -*- coding: utf-8 -*-
# @Time    : 2023/9/27 15:35
# @Author  : hhr
import os

import pythoncom
from win32com import client
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font
from openpyxl.styles.borders import Border, Side




def create_excel(datas, title):
    wb = Workbook()
    ws = wb['Sheet']
    ws.set_printer_settings(ws.PAPERSIZE_A4, ws.ORIENTATION_LANDSCAPE)
    xw = wb.active
    thick_side = Side(border_style="thin", color="000000")
    border = Border(top=thick_side, left=thick_side, right=thick_side, bottom=thick_side)
    ws.title = 'Sheet1'
    ws.merge_cells('A1:H1')
    ws.row_dimensions[1].height = 31
    top_left_cell = ws['A1']
    top_left_cell.value = f"{title}"
    top_left_cell.font = Font(b=True, color="000000", size=16)
    top_left_cell.alignment = Alignment(horizontal="center", vertical="center")

    ws.column_dimensions['A'].width = 5
    ws.column_dimensions['B'].width = 30
    ws.column_dimensions['C'].width = 20
    ws.column_dimensions['D'].width = 15
    ws.column_dimensions['E'].width = 10
    ws.column_dimensions['F'].width = 19
    ws.column_dimensions['G'].width = 19
    ws.column_dimensions['H'].width = 10

    ws['A2'] = '序号'
    ws['A2'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    ws['B2'] = '议题名称'
    ws['B2'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    ws['C2'] = '议题编号'
    ws['C2'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    ws['D2'] = '提交科室'
    ws['D2'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    ws['E2'] = '提交人'
    ws['E2'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    ws['F2'] = '提交时间'
    ws['F2'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    ws['G2'] = '审核时间'
    ws['G2'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    ws['H2'] = '汇报人'
    ws['H2'].alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)

    ws.row_dimensions[2].height = 30

    row = 3
    for x in datas:
        ws[f'A{row}'].alignment = Alignment(horizontal="center", vertical="center")
        xw[f'A{row}'].border = border
        ws[f'B{row}'].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        xw[f'B{row}'].border = border
        ws[f'C{row}'].alignment = Alignment(horizontal="center", vertical="center")
        xw[f'C{row}'].border = border
        ws[f'D{row}'].alignment = Alignment(horizontal="center", vertical="center")
        xw[f'D{row}'].border = border
        ws[f'E{row}'].alignment = Alignment(horizontal="center", vertical="center")
        xw[f'E{row}'].border = border
        ws[f'F{row}'].alignment = Alignment(horizontal="center", vertical="center")
        xw[f'F{row}'].border = border
        ws[f'G{row}'].alignment = Alignment(horizontal="center", vertical="center")
        xw[f'G{row}'].border = border
        ws[f'H{row}'].alignment = Alignment(horizontal="center", vertical="center")
        xw[f'H{row}'].border = border

        ws.cell(row, 1).value = row - 2
        ws.cell(row, 2).value = x['name']
        ws.cell(row, 3).value = x['number']
        ws.cell(row, 4).value = x['dep']
        ws.cell(row, 5).value = x['tjr']
        ws.cell(row, 6).value = x['tjsj']
        ws.cell(row, 7).value = x['shsj']
        ws.cell(row, 8).value = x['hbr']
        ws.row_dimensions[row].height = 30
        row += 1

    line_list = ["A", "B", "C", "D", "E", "F", "G", "H"]
    for j in range(1, len(datas) + 3):
        for k in line_list:
            xw[f'{k}{j}'].border = border

    file_path = os.getcwd() + f'/media/catalog/'
    excel_path = file_path + f"{title}.xlsx"
    wb.save(excel_path)

    pythoncom.CoInitialize()
    xlApp = client.Dispatch("Excel.Application")
    books = xlApp.Workbooks.Open(excel_path)
    ws = books.Worksheets[0]
    pdf_path = file_path+f"{title}.pdf"
    ws.ExportAsFixedFormat(0, pdf_path)
    books.Save()
    books.Close()
    return pdf_path.replace(os.getcwd(), '')
